MasterCashflow1
1. Description
Master Cashflow 1 cashflow type generates cashflows for each record based on the mapping provided for component,cashflow_amount,due_date,outstanding_amount,maturity_date.
2. Screen Configuration
Here, while generating cashflows it will check the COMPONENT, If it is PRINCIPAL then CASHFLOW AMOUNT is mapped to Principal Amount else if COMPONENT is INT then CASHFLOW AMOUNT is mapped to Interest Rate. DUE_DATE is mapped to cashflow_date. Since Adjustment Required is TRUE, It will add all the principal amounts for an account and this sum will be subtracted from OutBal and one more extra adjusted cashflow row will be added at the last. Click ⬇️ to download the test-bed.
3. Cashflow Derivation Logic
Case 1: If 'Is Adjustment Required' is mapped as true,
IS_ADJUSTMENT_REQUIRED: true
ACCOUNT_ID|OUTSTANDING_AMOUNT|COMPONENT|CASHFLOW_AMOUNT|DUE_DATE|MATURITY_date
ACC1001|5000.00|PRINCIPAL|2000.00|31-01-2024|31-05-2024
ACC1001|5000.00|INT|200.00|31-01-2024|31-05-2024
ACC1001|5000.00|PRINCIPAL|500.00|29-02-2024|31-05-2024
ACC1001|5000.00|PRINCIPAL|500.00|31-03-2024|31-05-2024
ACC1001|5000.00|PRINCIPAL|500.00|30-04-2024|31-05-2024
ACC1001|5000.00|PRINCIPAL|500.00|31-05-2024|31-05-2024
ACC1001|5000.00|INT|100.00|31-05-2024|31-05-2024
for generation of cashflows, the component will be checked if it is 'PRINCIPAL' then the cashflow amount will be stamped as principal_amount else if it is 'INT' it will be stamped as interest_amount
For the account 'ACC1001', the cashflows generated will be
principal_amount|interest_amount|cashflow_date
2000.00|0.00|31-01-2024
0.00|200.00|31-01-2024
500.00|0.00|29-02-2024
500.00|0.00|31-03-2024
500.00|0.00|30-04-2024
500.00|0.00|31-05-2024
0.00|100.00|31-05-2024
1000.00|0.00|31-05-2024 --> adjusted cashflow
Here the last cashflow will adjusted amount,
logic to derive the adjustment amount:
total principal amount = 2000.00+500.00+500.00+500.00+500.00 = 4000.00
adjustment_amount = outstanding_amount - total_principal_amount
= 5000.00 - 4000.00
= 1000.00
Case 2: If 'Is Adjustment Required' is mapped as false,
ACCOUNT_ID|OUTSTANDING_AMOUNT|COMPONENT|CASHFLOW_AMOUNT|DUE_DATE|MATURITY_date
ACC1001|5000.00|PRINCIPAL|2000.00|31-01-2024|31-05-2024
ACC1001|5000.00|INT|200.00|31-01-2024|31-05-2024
ACC1001|5000.00|PRINCIPAL|500.00|29-02-2024|31-05-2024
ACC1001|5000.00|PRINCIPAL|500.00|31-03-2024|31-05-2024
ACC1001|5000.00|PRINCIPAL|500.00|30-04-2024|31-05-2024
ACC1001|5000.00|PRINCIPAL|500.00|31-05-2024|31-05-2024
ACC1001|5000.00|INT|100.00|31-05-2024|31-05-2024
for generation of cashflows, the component will be checked if it is 'PRINCIPAL' then the cashflow amount will be stamped as principal_amount else if it is 'INT' it will be stamped as interest_amount.
For the account 'ACC1001', the cashflows generated will be
principal_amount|interest_amount|cashflow_date
2000.00|0.00|31-01-2024
0.00|200.00|31-01-2024
500.00|0.00|29-02-2024
500.00|0.00|31-03-2024
500.00|0.00|30-04-2024
500.00|0.00|31-05-2024
0.00|100.00|31-05-2024
4. Required Fields
| # | Parameters | Description | Is_Mandatory_Field | Possible_Values |
|---|---|---|---|---|
| 1 | cashflow_amount | The amount field which needs to be stamped as principal/interest amount. | YES | - |
| 2 | cashflow_type | The field to decide whether the cashflow amount to be stamped as principal amount or interest amount. | YES | PRINCIPAL or MAIN_INT |
| 3 | cashflow_date | The date field which needs to stamped as cashflow date. | YES | - |
| 4 | cashflow_account_id | The field which needs to be considered as the key to store the cashflows. | YES | - |
| 5 | master_account_id | The field which needs to be stamped as account id, and to be used to do lookup on cashflow file | YES | - |
| 6 | master_outstanding_amount | The total outstanding amount of the account. | YES | - |
| 7 | master_maturity_date | The date field which needs to be considered as account end date. | YES | - |
| 8 | is_adjustment_required | The flag which will decided if adjustment cashflow is required or not, if total principal amount is not equal to outstanding amount. | NO | true or false |
| 9 | interest_cashflow_type_values | Values for interest cashflow type. | NO | - |
| 10 | principal_cashflow_type_values | Values for principal cashflow type. | NO | - |
5. Working Excel
Click ⬇️ to download the excel calculation.